﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace ChuyenLenhSanXuat
{
    public class AccessData
    {
        private SqlConnection cnn;
        private SqlCommand cmd;
        private DataSet dts;
        private DataTable tbl;
        private SqlDataReader dr;
        private SqlDataAdapter da;
        private SqlTransaction tran;
        private string ConnectString = System.Configuration.ConfigurationSettings.AppSettings["chuyenlsx"];
      
        public SqlConnection fBolKetNoi()
        {
            return new SqlConnection(ConnectString);
        }
       



        public Boolean fBolThucThiSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                if (arrThamSo != null) //Tru?ng h?p có tham s?
                {
                    cmd.Parameters.Clear();
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                cmd.ExecuteNonQuery();
                cnn.Close();
                return true;
            }
            catch
            {

                return false;
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
            }
        }

        //Th?c thi câu l?nh SQL         
        public Boolean fBolThucThiSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                tran = cnn.BeginTransaction();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                tran.Dispose();
            }
        }
        // Thuc thi cau lenh co tham so 
        public Boolean fBolThucThiSPSQL(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                tran = cnn.BeginTransaction();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                cmd.Transaction = tran;
                if (arrThamSo != null) //Tru?ng h?p có tham s?
                {
                    cmd.Parameters.Clear();
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);

                }

                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
            catch (Exception)
            {
                //  string a = cmd.ExecuteNonQuery().ToString();
                //  string b = ex.ToString();
                tran.Rollback();
                return false;


            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                tran.Dispose();
            }
        }
        //Ð?c d? li?u vào dataset b?ng StoredProcedure có tham so
        public DataSet fdtsDocDuLieuSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                if (arrThamSo != null) //Tru?ng h?p có tham s?
                {
                    cmd.Parameters.Clear();
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                da = new SqlDataAdapter(cmd);
                dts = new DataSet();
                da.Fill(dts);
                cnn.Close();
                return dts;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Ð?c d? li?u vào dataset b?ng SQL
        public DataSet fdtsDocDuLieuSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                da = new SqlDataAdapter(cmd);
                dts = new DataSet();
                da.Fill(dts);
                cnn.Close();
                return dts;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Ð?c d? li?u vào datatable b?ng StoredProcedure
        public DataTable ftblDocDuLieuSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                if (arrThamSo != null) //Tru?ng h?p có tham s?
                {
                    cmd.Parameters.Clear();
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);
                cnn.Close();
                return tbl;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Ð?c d? li?u vào datatable b?ng SQL
        public DataTable ftblDocDuLieuSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);
                cnn.Close();
                return tbl;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (cnn.State != ConnectionState.Closed) { cnn.Close(); }
                cnn.Dispose();
                cmd.Dispose();
                da.Dispose();
            }
        }

        //Ð?c d? li?u vào datareader b?ng StoredProcedure
        public SqlDataReader fdreadDocDuLieuSP(string strTenSP, SqlParameter[] arrThamSo)
        {
            int intLoop;
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strTenSP;
                cmd.Connection = cnn;
                if (arrThamSo != null) //Tru?ng h?p có tham s?
                {
                    cmd.Parameters.Clear();
                    for (intLoop = 0; intLoop < arrThamSo.Length; intLoop++)
                        cmd.Parameters.Add(arrThamSo[intLoop]);
                }
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //Ð?c d? li?u vào datareader b?ng SQL
        public SqlDataReader fdreadDocDuLieuSQL(string strSQL)
        {
            try
            {
                cnn = fBolKetNoi();
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                cmd.Connection = cnn;
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
